using MediatR;
using System.Data;
using Dapper;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using RBAC.Read.Api.Application.Command.ERP.SalesManagement.H;
using RBAC.Interstructrue.Base;

namespace RBAC.Read.Api.Application.Handler.ERP.SalesManagement.H
{
    /// <summary>
    /// 导出：销售订单计划查询结果
    /// </summary>
    public class ExportSalesOrderPlanListHandler : IRequestHandler<ExportSalesOrderPlanListCommand, byte[]>
    {
        private readonly IDbConnectionFactory _dbConnectionFactory;

        public ExportSalesOrderPlanListHandler(IDbConnectionFactory dbConnectionFactory)
        {
            _dbConnectionFactory = dbConnectionFactory;
        }

        public async Task<byte[]> Handle(ExportSalesOrderPlanListCommand request, CancellationToken cancellationToken)
        {
            using var connection = _dbConnectionFactory.CreateConnection();

            var where = new List<string>();
            var p = new DynamicParameters();

            if (!string.IsNullOrWhiteSpace(request.SalesPerson))
            {
                where.Add("so.SalesPerson LIKE @SalesPerson");
                p.Add("@SalesPerson", $"%{request.SalesPerson.Trim()}%");
            }
            if (!string.IsNullOrWhiteSpace(request.SalesOrderNumber))
            {
                where.Add("so.OrderCode LIKE @SalesOrderNumber");
                p.Add("@SalesOrderNumber", $"%{request.SalesOrderNumber.Trim()}%");
            }
            if (!string.IsNullOrWhiteSpace(request.BusinessType))
            {
                where.Add("so.BusinessType LIKE @BusinessType");
                p.Add("@BusinessType", $"%{request.BusinessType.Trim()}%");
            }
            if (!string.IsNullOrWhiteSpace(request.DeliveryStatus))
            {
                where.Add("so.DeliveryStatus = @DeliveryStatus");
                p.Add("@DeliveryStatus", request.DeliveryStatus.Trim());
            }
            if (!string.IsNullOrWhiteSpace(request.MaterialCode))
            {
                where.Add("m.MaterCode LIKE @MaterialCode");
                p.Add("@MaterialCode", $"%{request.MaterialCode.Trim()}%");
            }
            if (!string.IsNullOrWhiteSpace(request.MaterialName))
            {
                where.Add("m.MaterName LIKE @MaterialName");
                p.Add("@MaterialName", $"%{request.MaterialName.Trim()}%");
            }
            if (request.StartDeliveryDate.HasValue)
            {
                where.Add("so.DeliveryDate >= @StartDeliveryDate");
                p.Add("@StartDeliveryDate", request.StartDeliveryDate.Value.Date);
            }
            if (request.EndDeliveryDate.HasValue)
            {
                where.Add("so.DeliveryDate <= @EndDeliveryDate");
                p.Add("@EndDeliveryDate", request.EndDeliveryDate.Value.Date.AddDays(1).AddSeconds(-1));
            }

            var offset = (request.PageIndex - 1) * request.PageSize;

            var sql = $@"
                SELECT
                    (@row_number:=@row_number + 1) AS SerialNumber,
                    so.SalesPerson AS SalesPerson,
                    so.OrderCode AS SalesOrderNumber,
                    so.BusinessType AS BusinessType,
                    so.DeliveryDate AS DeliveryDate,
                    so.DeliveryStatus AS DeliveryStatus,
                    m.MaterCode AS MaterialCode,
                    m.MaterName AS MaterialName,
                    IFNULL(sod.Remark, '') AS QualityRequirement,
                    IFNULL(m.SpecDesc, '') AS PackagingRequirement,
                    IFNULL(so.Remark, '') AS Remark,
                    sod.Num AS OrderQuantity
                FROM SalesOrder so
                INNER JOIN SalesOrderDetail sod ON so.Id = sod.SalesOrderId AND sod.IsDeleted = 0
                INNER JOIN Mater m ON sod.MaterId = m.Id AND m.IsDeleted = 0
                CROSS JOIN (SELECT @row_number := {offset}) r
                WHERE so.IsDeleted = 0
                {{extraWhere}}
                ORDER BY so.OrderDate DESC, so.Id DESC
                LIMIT @PageSize OFFSET @Offset".Replace("{extraWhere}",
                    where.Count == 0 ? string.Empty : " AND " + string.Join(" AND ", where));

            p.Add("@Offset", offset);
            p.Add("@PageSize", request.PageSize);

            var rows = await connection.QueryAsync<SalesOrderPlanItemDto>(sql, p);

            // 生成 Excel
            var workbook = new XSSFWorkbook();
            var sheet = workbook.CreateSheet("销售订单计划");

            var headerStyle = workbook.CreateCellStyle();
            var headerFont = workbook.CreateFont();
            headerFont.IsBold = true;
            headerStyle.SetFont(headerFont);

            var headers = new[]
            {
                "序号","销售员","销售订单号","业务类型","订单交期",
                "交期状态","物料编码","物料名称","质量要求","包装要求","备注","订单数量"
            };

            var headerRow = sheet.CreateRow(0);
            for (int i = 0; i < headers.Length; i++)
            {
                var cell = headerRow.CreateCell(i);
                cell.SetCellValue(headers[i]);
                cell.CellStyle = headerStyle;
            }

            int rowIndex = 1;
            foreach (var r in rows)
            {
                var row = sheet.CreateRow(rowIndex++);
                row.CreateCell(0).SetCellValue(r.SerialNumber);
                row.CreateCell(1).SetCellValue(r.SalesPerson ?? "");
                row.CreateCell(2).SetCellValue(r.SalesOrderNumber ?? "");
                row.CreateCell(3).SetCellValue(r.BusinessType ?? "");
                row.CreateCell(4).SetCellValue(r.DeliveryDate.HasValue ? r.DeliveryDate.Value.ToString("yyyy-MM-dd") : "");
                row.CreateCell(5).SetCellValue(r.DeliveryStatus ?? "");
                row.CreateCell(6).SetCellValue(r.MaterialCode ?? "");
                row.CreateCell(7).SetCellValue(r.MaterialName ?? "");
                row.CreateCell(8).SetCellValue(r.QualityRequirement ?? "");
                row.CreateCell(9).SetCellValue(r.PackagingRequirement ?? "");
                row.CreateCell(10).SetCellValue(r.Remark ?? "");
                row.CreateCell(11).SetCellValue(r.OrderQuantity.HasValue ? r.OrderQuantity.Value.ToString() : "0");
            }

            for (int i = 0; i < headers.Length; i++)
                sheet.AutoSizeColumn(i);

            using var ms = new MemoryStream();
            workbook.Write(ms);
            return ms.ToArray();
        }
    }
}
